﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Windows.Forms;

namespace ClientApp
{
    class DatabaseAccess : IDatabase
    {
        private SqlConnection sqlConnection;

        private static DatabaseAccess instance;

        private DatabaseAccess() { }

        public static DatabaseAccess Instance
        {
            get
            {
                if (instance != null)
                    return instance;
                else
                    return (instance = new DatabaseAccess());
            }
        }

        #region WorkWithTables

        public void Initialize(string connectionString)
        {
            sqlConnection = new SqlConnection(connectionString);

            sqlConnection.Open();

            DropTables();

            var command = sqlConnection.CreateCommand();
            command.CommandText = File.ReadAllText("../../Install.sql");

            try
            {
                var reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception)
            {

            }

        }

        public void CreateInitialData()
        {
            Add(new Skill("С++", "Знание C++"));
            Add(new Skill("AGILE", "XP, SCRUM, SPRINTS"));
            Add(new Skill("Python", "IRONPYTHON for .NET"));
            Add(new Skill("Английский язык", "Advanced+"));
            Add(new Skill("Немецкий язык", "Intermediate+"));
            Add(new Skill("Web-design", "PHP, HTML, ASP.NET, Javascript"));
            Add(new Skill("Linux", "Знание базовых команд shell"));
            Add(new Skill("Анализ рынка", "Умение предсказывать ценовую динамику продукта"));
            Add(new Skill("Администрирование ОС", "Знание устройства ОС"));
            Add(new Skill("HR", "Подбор персонала"));
            Add(new Skill("С#", "Знание C#"));
            
            Add(new Activity("Designer", "Web design"));
            Add(new Activity("Programmer", "Programming"));
            Add(new Activity("Tester", "Testing of Software"));
            Add(new Activity("Integrator", "Software Installation and Upgrading"));
            Add(new Activity("Architect", "Architecture"));


            Add(new Employer("MERA",
                            new List<Activity>()
                            {
                                new Activity {Id=2},
                                new Activity {Id=3}
                            },
                            "Sovetskaya, 12",
                            "+75557778672",
                            "mera_nn"));

            Add(new Employer("Intel",
                            new List<Activity>()
                            {
                                new Activity {Id=1},
                                new Activity {Id=2}
                            },
                            "Sennaya, 16",
                            "+75557778667",
                            "intel_rus"));


            Add(new Vacancy()
            {
                Title = "C++ Programmer",
                Employer = new Employer { Id = 1 },
                Activity = new Activity { Id = 2 },
                CreationDate = DateTime.Now,
                Location = "Sovetskaya, 12",
                Salary = 20000,
                SkillIdsRequired = new SortedSet<uint>() { 1, 2, 4 }
            });

            Add(new Vacancy()
            {
                Title = "Midi Architecture",
                Employer = new Employer { Id = 2 },
                Activity = new Activity { Id = 5 },
                CreationDate = DateTime.Now,
                Location = "Sennaya, 16",
                Salary = 60000,
                SkillIdsRequired = new SortedSet<uint>() { 2, 4, 5, 6 }
            });

            Add(new Vacancy()
            {
                Title = "Tester",
                Employer = new Employer { Id = 1 },
                Activity = new Activity { Id = 3 },
                CreationDate = DateTime.Now,
                Location = "Podval, 16",
                Salary = 20000,
                SkillIdsRequired = new SortedSet<uint>() { 4, 6 }
            });

            Add(new Candidate()
            {
                FirstName = "Kobe",
                LastName = "Bryant",
                MiddleName = "Black Mamba",
                Activity = new Activity { Id = 2 },
                Address = "Los Angeles, Lakers",
                SalaryExpected = 15000,
                Skills = new List<Skill>()
                {
                    new Skill { Id = 1 },
                    new Skill { Id = 4 }
                },
                Login = "KobeB"
            });

            Add(new Candidate()
            {
                FirstName = "LeBron",
                LastName = "James",
                MiddleName = "The King",
                Activity = new Activity { Id = 4 },
                Address = "Miami HEAT",
                SalaryExpected = 35000,
                Skills = new List<Skill>()
                {
                    new Skill { Id = 2 },
                    new Skill { Id = 5 }
                },
                Login = "LeB"
            });

            Add(new Candidate()
            {
                FirstName = "Иван",
                LastName = "Шариков",
                MiddleName = "Александрович",
                Activity = new Activity { Id = 3 },
                Address = "Пр. Гагарина, 23",
                SalaryExpected = 20000,
                Skills = new List<Skill>()
                {
                    new Skill { Id = 4 },
                    new Skill { Id = 6 }
                },
                Login = "ivashka"
            });

            Add(new Candidate()
            {
                FirstName = "Павел",
                LastName = "Игнатов",
                MiddleName = "Александрович",
                Activity = new Activity { Id = 2 },
                Address = "Пр. Гагарина, 57",
                SalaryExpected = 2000,
                Skills = new List<Skill>()
                {
                    new Skill { Id = 1 },
                    new Skill { Id = 3 },
                    new Skill { Id = 4 },
                    new Skill { Id = 6 }
                },
                Login = "red_diabo17"
            });

            Add(new Candidate()
            {
                FirstName = "Дмитрий",
                LastName = "Шагов",
                MiddleName = "Николаевич",
                Activity = new Activity { Id = 2 },
                Address = "Пр. Гагарина, 23",
                SalaryExpected = 30000,
                Skills = new List<Skill>()
                {
                    new Skill { Id = 7 }
                },
                Login = "flexru"
            });

            Add(new Bargain()
            {
                Employer = new Employer { Id = 1 },
                Vacancy = new Vacancy { Id = 1 },
                Candidate = new Candidate { Id = 1 },
                DateTime = DateTime.Now
            });

            Add(new Bargain()
            {
                Employer = new Employer { Id = 1 },
                Vacancy = new Vacancy { Id = 3 },
                Candidate = new Candidate { Id = 3 },
                DateTime = DateTime.Now
            });

            List<Skill> skills = SkillsListedByVacancies();
        }

        public void DropTables()
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();
            command.CommandText = File.ReadAllText("../../DropTables.sql");

            try
            {
                var reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception)
            {

            }
        }

        #endregion

        #region Get

        #region GetByID

        public Activity ActivityByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Activities WHERE id = @id";

            var activity = new Activity();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        activity.Id = (uint)reader.GetInt32(0);
                        activity.Title = reader.GetString(1);
                        activity.Description = reader.GetString(2);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return activity;
        }

        public Skill SkillByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);

            command.CommandText = "SELECT * FROM Skills WHERE id = @id";

            var skill = new Skill();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        skill.Id = (uint)reader.GetInt32(0);
                        skill.Title = reader.GetString(1);
                        skill.Description = reader.GetString(2);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return skill;
        }

        public Candidate CandidateByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);

            command.CommandText = "SELECT * FROM Candidates WHERE id = @id";

            var candidate = new Candidate();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        candidate.Id = (uint)reader.GetInt32(0);
                        candidate.FirstName = reader.GetString(1);
                        candidate.LastName = reader.GetString(2);
                        candidate.MiddleName = reader.GetString(3);
                        candidate.Address = reader.GetString(4);
                        candidate.SalaryExpected = (uint)reader.GetInt32(7);
                        candidate.Activity = new Activity { Id = (uint)reader.GetInt32(8) };
                        candidate.Login = reader.GetString(9);
                    }
                    else
                    {
                        candidate.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (candidate.Id != 0)
            {
                candidate.Activity = ActivityByID(candidate.Activity.Id);
                candidate.Skills = SkillsByCandidateID((candidate.Id));
            }

            return candidate;
        }

        public Employer EmployerByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);

            command.CommandText = "SELECT * FROM Employers WHERE id = @id";

            var employer = new Employer();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        employer.Id = (uint)reader.GetInt32(0);
                        employer.Name = reader.GetString(1);
                        employer.Address = reader.GetString(2);
                        employer.Phone = reader.GetString(5);
                        employer.Login = reader.GetString(6);
                    }
                    else
                    {
                        employer.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (employer.Id != 0)
            {
                employer.Activities = ActivitiesByEmployerID(employer.Id);
            }

            return employer;
        }

        public Vacancy VacancyByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);

            command.CommandText = "SELECT * FROM Vacancies WHERE id = @id";

            var vacancy = new Vacancy();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        vacancy.Id = (uint)reader.GetInt32(0);
                        vacancy.Title = reader.GetString(1);
                        vacancy.Employer = new Employer { Id = (uint)reader.GetInt32(2) };
                        vacancy.Activity = new Activity { Id = (uint)reader.GetInt32(3) };
                        vacancy.CreationDate = reader.GetDateTime(4);
                        vacancy.Location = reader.GetString(5);
                        vacancy.Salary = (uint)reader.GetInt32(8);
                    }
                    else
                    {
                        vacancy.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (vacancy.Id != 0)
            {
                vacancy.Employer = EmployerByID(vacancy.Employer.Id);
                vacancy.Activity = ActivityByID(vacancy.Activity.Id);
                vacancy.SkillIdsRequired = SkillsByVacancyID(vacancy.Id);
            }

            return vacancy;
        }

        public Bargain BargainByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Bargains WHERE id = @id";

            var bargain = new Bargain();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        bargain.Id = (uint)reader.GetInt32(0);
                        bargain.Employer = new Employer { Id = (uint)reader.GetInt32(1) };
                        bargain.Vacancy = new Vacancy { Id = (uint)reader.GetInt32(2) };
                        bargain.Candidate = new Candidate { Id = (uint)reader.GetInt32(3) };
                        bargain.DateTime = reader.GetDateTime(4);
                    }
                    else
                    {
                        bargain.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (bargain.Id != 0)
            {
                bargain.Employer = EmployerByID(bargain.Employer.Id);
                bargain.Vacancy = VacancyByID(bargain.Vacancy.Id);
                bargain.Candidate = CandidateByID(bargain.Candidate.Id);
            }

            return bargain;
        }

        private SortedSet<uint> SkillsByVacancyID(uint vacancy_id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id_vac", SqlDbType.Int, vacancy_id);

            command.CommandText = "SELECT * FROM Skills as sk, VacancySkillRel as rel WHERE rel.id_vac = @id_vac AND sk.id = rel.id_skill";

            var skills = new SortedSet<uint>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        skills.Add((uint)reader.GetInt32(0));
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return skills;
        }

        private List<Activity> ActivitiesByEmployerID(uint employer_id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id_emp", SqlDbType.Int, employer_id);

            command.CommandText = "SELECT * FROM Activities as act, EmployerActivityRel as rel WHERE rel.id_emp = @id_emp AND act.id = rel.id_act";

            var activities = new List<Activity>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        Activity activity = new Activity { Id = (uint)reader.GetInt32(0), Title = reader.GetString(1), Description = reader.GetString(2) };
                        activities.Add(activity);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return activities;
        }

        private List<Skill> SkillsByCandidateID(uint candidate_id)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id_can", SqlDbType.Int, candidate_id);

            command.CommandText = "SELECT * FROM Skills as sk, CandidateSkillRel as rel WHERE rel.id_can = @id_can AND sk.id = rel.id_skill";

            var skills = new List<Skill>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        Skill skill = new Skill { Id = (uint)reader.GetInt32(0), Title = reader.GetString(1), Description = reader.GetString(2) };
                        skills.Add(skill);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return skills;
        }

        #endregion

        #region Get By Title or Login

        public Vacancy VacancyByTitle(string title)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@title", SqlDbType.Int, title);

            command.CommandText = "SELECT * FROM Vacancies WHERE title = @title";

            var vacancy = new Vacancy();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        vacancy.Id = (uint)reader.GetInt32(0);
                        vacancy.Title = reader.GetString(1);
                        vacancy.Employer = new Employer { Id = (uint)reader.GetInt32(2) };
                        vacancy.Activity = new Activity { Id = (uint)reader.GetInt32(3) };
                        vacancy.CreationDate = reader.GetDateTime(4);
                        vacancy.Location = reader.GetString(5);
                        vacancy.Salary = (uint)reader.GetInt32(8);
                    }
                    else
                    {
                        vacancy.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (vacancy.Id != 0)
            {
                vacancy.Employer = EmployerByID(vacancy.Employer.Id);
                vacancy.Activity = ActivityByID(vacancy.Activity.Id);
                vacancy.SkillIdsRequired = SkillsByVacancyID(vacancy.Id);
            }

            return vacancy;
        }

        public Employer EmployerByTitle(string title)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.Int, title);

            command.CommandText = "SELECT * FROM Employers WHERE name = @name";

            var employer = new Employer();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        employer.Id = (uint)reader.GetInt32(0);
                        employer.Name = reader.GetString(1);
                        employer.Address = reader.GetString(2);
                        employer.Phone = reader.GetString(5);
                        employer.Login = reader.GetString(6);
                    }
                    else
                    {
                        employer.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (employer.Id != 0)
            {
                employer.Activities = ActivitiesByEmployerID(employer.Id);
            }

            return employer;
        }

        public Activity ActivityByTitle(string title)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@title", SqlDbType.VarChar, title);
            command.CommandText = "SELECT * FROM Activities WHERE title = @title";

            var activity = new Activity();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        activity.Id = (uint)reader.GetInt32(0);
                        activity.Title = reader.GetString(1);
                        activity.Description = reader.GetString(2);
                    }
                    else
                    {
                        activity.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return activity;
        }

        public Skill SkillByTitle(string title)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@title", SqlDbType.VarChar, title);

            command.CommandText = "SELECT * FROM Skills WHERE title = @title";

            var skill = new Skill();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        skill.Id = (uint)reader.GetInt32(0);
                        skill.Title = reader.GetString(1);
                        skill.Description = reader.GetString(2);
                    }
                    else
                    {
                        skill.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return skill;
        }

        public Employer EmployerByLogin(string login)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@login", SqlDbType.VarChar, login);

            command.CommandText = "SELECT * FROM Employers WHERE login = @login";

            var employer = new Employer();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        employer.Id = (uint)reader.GetInt32(0);
                        employer.Name = reader.GetString(1);
                        employer.Address = reader.GetString(2);
                        employer.Phone = reader.GetString(5);
                        employer.Login = reader.GetString(6);
                    }
                    else
                    {
                        employer.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (employer.Id != 0)
            {
                employer.Activities = ActivitiesByEmployerID(employer.Id);
            }

            return employer;
        }

        public Candidate CandidateByLogin(string login)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@login", SqlDbType.VarChar, login);

            command.CommandText = "SELECT * FROM Candidates WHERE login = @login";

            var candidate = new Candidate();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        candidate.Id = (uint)reader.GetInt32(0);
                        candidate.FirstName = reader.GetString(1);
                        candidate.LastName = reader.GetString(2);
                        candidate.MiddleName = reader.GetString(3);
                        candidate.Address = reader.GetString(4);
                        candidate.SalaryExpected = (uint)reader.GetInt32(7);
                        candidate.Activity = new Activity { Id = (uint)reader.GetInt32(8) };
                        candidate.Login = reader.GetString(9);
                    }
                    else
                    {
                        candidate.Id = 0;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (candidate.Id != 0)
            {
                candidate.Activity = ActivityByID(candidate.Activity.Id);
                candidate.Skills = SkillsByCandidateID((candidate.Id));
            }

            return candidate;
        }

        #endregion

        #region Common Get

        public List<Skill> Skills()
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            List<Skill> skills = new List<Skill>();

            var command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT * FROM Skills";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        Skill skill = new Skill(reader.GetString(1), reader.GetString(2));
                        skill.Id = (uint)reader.GetInt32(0);
                        skills.Add(skill);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return skills;
        }

        public List<Activity> Activities()
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            List<Activity> activities = new List<Activity>();

            var command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT * FROM Activities";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        Activity activity = new Activity(reader.GetString(1), reader.GetString(2));
                        activity.Id = (uint)reader.GetInt32(0);
                        activities.Add(activity);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return activities;
        }

        public List<Employer> Employers()
        {
            List<Employer> employers = new List<Employer>();

            var command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT * FROM Employers";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        employers.Add(new Employer { Id = (uint)reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            for (int i = 0; i < employers.Count; ++i)
            {
                employers[i] = EmployerByID(employers[i].Id);
            }

            return employers;
        }

        public List<Candidate> Candidates()
        {
            List<Candidate> candidates = new List<Candidate>();

            var command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT * FROM Candidates";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        candidates.Add(new Candidate { Id = (uint)reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            for (int i = 0; i < candidates.Count; ++i)
            {
                candidates[i] = CandidateByID(candidates[i].Id);
            }

            return candidates;
        }

        public List<Bargain> Bargains()
        {
            List<Bargain> bargains = new List<Bargain>();

            var command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT * FROM Bargains";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        bargains.Add(new Bargain { Id = (uint)reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            for (int i = 0; i < bargains.Count; ++i)
            {
                bargains[i] = BargainByID(bargains[i].Id);
            }

            return bargains;
        }


        public List<Vacancy> Vacancies()
        {
            List<Vacancy> vacancies = new List<Vacancy>();

            var command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT * FROM Vacancies";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        vacancies.Add(new Vacancy { Id = (uint)reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            for (int i = 0; i < vacancies.Count; ++i)
            {
                vacancies[i] = VacancyByID(vacancies[i].Id);
            }

            return vacancies;
        }

        #endregion

        #region Others Get

        public List<Vacancy> ActiveVacancies()
        {
            List<Vacancy> vacancies = Vacancies();
            List<Bargain> bargains = Bargains();
            List<Vacancy> activeVacancies = new List<Vacancy>();
            foreach (var vacancy in vacancies)
            {
                bool found = false;
                foreach (var bargain in bargains)
                {
                    if (bargain.Vacancy.Id == vacancy.Id)
                        found = true;
                }
                if (!found)
                    activeVacancies.Add(vacancy);
            }
            return activeVacancies;
        }

        public List<Vacancy> FilledVacancies()
        {
            List<Vacancy> vacancies = Vacancies();
            List<Bargain> bargains = Bargains();
            List<Vacancy> filledVacancies = new List<Vacancy>();
            foreach (var vacancy in vacancies)
            {
                bool found = false;
                foreach (var bargain in bargains)
                {
                    if (bargain.Vacancy.Id == vacancy.Id)
                        found = true;
                }
                if (found)
                    filledVacancies.Add(vacancy);
            }
            return filledVacancies;
        }

        // по всем открытым вакансиям вернуть общий список навыков
        public List<Skill> SkillsListedByVacancies()
        {
            List<Vacancy> activeVacancies = ActiveVacancies();

            SortedSet<uint> skillIDs = new SortedSet<uint>();
            foreach (var vacancy in activeVacancies)
            {
                foreach (var skill in vacancy.SkillIdsRequired)
                {
                    if (!skillIDs.Contains(skill))
                    {
                        skillIDs.Add(skill);
                    }
                }
            }
            List<Skill> skills = new List<Skill>();
            foreach (var skillID in skillIDs)
                skills.Add(SkillByID(skillID));

            return skills;
        }

        public List<Vacancy> Vacancies(Activity activity, List<Skill> skills, uint salary)
        {
            List<Vacancy> vacancies = ActiveVacancies();
            List<Vacancy> satVacancies = new List<Vacancy>();

            foreach (var vacancy in vacancies)
            {
                if ((vacancy.Activity.Id == activity.Id) && vacancy.Salary > salary)
                {
                    bool not_found = false;
                    foreach (var skill in skills)
                    {
                        if (!vacancy.SkillIdsRequired.Contains(skill.Id))
                            not_found = true;
                    }
                    if (!not_found)
                        satVacancies.Add(vacancy);
                }
            }

            return satVacancies;
        }

        public List<Candidate> Candidates(List<Skill> skills, Point location, int distance)
        {
            List<Candidate> satCandidates = new List<Candidate>();
            List<Candidate> candidates = Candidates();
            for (int i = 0; i < skills.Count; ++i)
            {
                skills[i] = SkillByTitle(skills[i].Title);
            }

            foreach (var candidate in candidates)
            {
                int locationX = candidate.Address.Length % 5;
                int locationY = candidate.Address[0] % 5;
                if (locationX * locationX + locationY * locationY < distance * distance)
                {
                    bool not_found = false;
                    for (int i = 0; i < skills.Count; ++i)
                    {
                        bool flag = false;
                        for (int j = 0; j < candidate.Skills.Count; ++j)
                        {
                            if (skills[i].Id == candidate.Skills[j].Id)
                                flag = true;
                        }
                        if (!flag)
                            not_found = true;
                    }
                    if (!not_found)
                        satCandidates.Add(candidate);
                }
            }

            return satCandidates;
        }

        public List<Bargain> EmployerBargains(Employer employer)
        {
            List<Bargain> bargains = new List<Bargain>();

            var command = sqlConnection.CreateCommand();
            AddSqlParameter(command, "@id_emp", SqlDbType.Int, employer.Id);
            command.CommandText = "SELECT * FROM Bargains WHERE id_emp=@id_emp";

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        bargains.Add(new Bargain { Id = (uint)reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            for (int i = 0; i < bargains.Count; ++i)
            {
                bargains[i] = BargainByID(bargains[i].Id);
            }

            return bargains;
        }

        public List<Vacancy> Vacancies(Activity activity)
        {
            List<Vacancy> vacancies = new List<Vacancy>();

            foreach (var activeVacancy in ActiveVacancies())
            {
                if (activeVacancy.Activity.Title == activity.Title)
                {
                    vacancies.Add(activeVacancy);
                }
            }
            
            return vacancies;
        }

        public SortedSet<uint> SkillIds(ListBox.SelectedObjectCollection items)
        {
            SortedSet<uint> result = new SortedSet<uint>();
            foreach (var item in items)
            {
                result.Add(SkillByTitle(item.ToString()).Id);
            }
            return result;
        }

        public List<KeyValuePair<string, int>> MostRequestedSkills()
        {
            List<KeyValuePair<string, int>> mostRequestedSkills = new List<KeyValuePair<string, int>>();

            List<Skill> skills = Skills();

            foreach (var skill in skills)
            {
                if (sqlConnection.State != ConnectionState.Open)
                    sqlConnection.Open();

                var command = sqlConnection.CreateCommand();

                AddSqlParameter(command, "@id_skill", SqlDbType.Int, skill.Id);

                command.CommandText = "SELECT count(*) as counter FROM VacancySkillRel WHERE id_skill=@id_skill";

                int count = (int)command.ExecuteScalar();

                mostRequestedSkills.Add(new KeyValuePair<string, int>(skill.Title, count));
            }

            mostRequestedSkills.Sort((firstPair, nextPair) =>
            {
                return nextPair.Value.CompareTo(firstPair.Value);
            }
            );

            return mostRequestedSkills;
        }

        public List<KeyValuePair<string, int>> TopDealEmployers()
        {
            List<KeyValuePair<string, int>> topDealEmployers = new List<KeyValuePair<string, int>>();

            List<Employer> employers = Employers();
            foreach (var employer in employers)
            {
                if (sqlConnection.State != ConnectionState.Open)
                    sqlConnection.Open();

                int sum = 0;

                var com = sqlConnection.CreateCommand();
                AddSqlParameter(com, "@id_emp", SqlDbType.Int, employer.Id);
                com.CommandText = "SELECT count(*) as counter FROM Bargains as bar, Vacancies as vac WHERE bar.id_emp=@id_emp AND bar.id_vac=vac.id";
                int count = (int)com.ExecuteScalar();

                if (count > 0)
                {
                    var command = sqlConnection.CreateCommand();
                    AddSqlParameter(command, "@id_emp", SqlDbType.Int, employer.Id);
                    command.CommandText = "SELECT sum(vac.salary) as s FROM Bargains as bar, Vacancies as vac WHERE bar.id_emp=@id_emp AND bar.id_vac=vac.id";
                    sum = (int)command.ExecuteScalar() * 2;
                }

                topDealEmployers.Add(new KeyValuePair<string, int>(employer.Name, sum));
            }

            topDealEmployers.Sort((firstPair, nextPair) =>
            {
                return nextPair.Value.CompareTo(firstPair.Value);
            }
            );

            return topDealEmployers;
        }

        #endregion

        #endregion

        #region Add

        #region AuxiliaryAdd

        private void AddEmployerActivityRelation(int employer_id, Activity activity, SqlTransaction trans)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();
            command.Transaction = trans;

            AddSqlParameter(command, "@id_emp", SqlDbType.Int, employer_id);
            AddSqlParameter(command, "@id_act", SqlDbType.Int, activity.Id);

            command.CommandText = "INSERT INTO EmployerActivityRel (id_emp, id_act) VALUES (@id_emp, @id_act)";
            command.ExecuteNonQuery();
        }

        private void AddCandidateSkillRelation(int candidate_id, Skill skill, SqlTransaction trans)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();
            command.Transaction = trans;

            AddSqlParameter(command, "@id_can", SqlDbType.Int, candidate_id);
            AddSqlParameter(command, "@id_skill", SqlDbType.Int, skill.Id);

            command.CommandText = "INSERT INTO CandidateSkillRel (id_can, id_skill) VALUES (@id_can, @id_skill)";
            command.ExecuteNonQuery();

        }

        private void AddVacancySkillRelation(int vacancy_id, uint skill_id, SqlTransaction trans)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            var command = sqlConnection.CreateCommand();
            command.Transaction = trans;

            AddSqlParameter(command, "@id_vac", SqlDbType.Int, vacancy_id);
            AddSqlParameter(command, "@id_skill", SqlDbType.Int, skill_id);

            command.CommandText = "INSERT INTO VacancySkillRel (id_vac, id_skill) VALUES (@id_vac, @id_skill)";
            command.ExecuteNonQuery();
        }

        #endregion

        public bool Add(Employer employer)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
            SqlTransaction trans = sqlConnection.BeginTransaction();
            try
            {
                var command = sqlConnection.CreateCommand();
                command.Transaction = trans;
                AddSqlParameter(command, "@name", SqlDbType.VarChar, employer.Name);
                AddSqlParameter(command, "@location", SqlDbType.VarChar, employer.Address);
                AddSqlParameter(command, "@locationX", SqlDbType.Int, employer.Address.Length % 5);
                AddSqlParameter(command, "@locationY", SqlDbType.Int, employer.Address[0] % 5);
                AddSqlParameter(command, "@phone", SqlDbType.VarChar, employer.Phone);
                AddSqlParameter(command, "@login", SqlDbType.VarChar, employer.Login);


                command.CommandText = "INSERT INTO Employers (name, location, locationX, locationY, phone, login) OUTPUT inserted.id VALUES (@name, @location, @locationX, @locationY, @phone, @login)";
                int newID = (int)command.ExecuteScalar();
                foreach (var activity in employer.Activities)
                {
                    AddEmployerActivityRelation(newID, activity, trans);
                }
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                return false;
            }
            return true;
        }

        public bool Add(Candidate candidate)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
            SqlTransaction trans = sqlConnection.BeginTransaction();
            try
            {
                var command = sqlConnection.CreateCommand();
                command.Transaction = trans;
                AddSqlParameter(command, "@firstname", SqlDbType.VarChar, candidate.FirstName);
                AddSqlParameter(command, "@lastname", SqlDbType.VarChar, candidate.LastName);
                AddSqlParameter(command, "@middlename", SqlDbType.VarChar, candidate.MiddleName);
                AddSqlParameter(command, "@location", SqlDbType.VarChar, candidate.Address);
                AddSqlParameter(command, "@locationX", SqlDbType.Int, candidate.Address.Length % 5);
                AddSqlParameter(command, "@locationY", SqlDbType.Int, candidate.Address[0] % 5);
                AddSqlParameter(command, "@expected_salary", SqlDbType.Int, candidate.SalaryExpected);
                AddSqlParameter(command, "@id_act", SqlDbType.Int, candidate.Activity.Id);
                AddSqlParameter(command, "@login", SqlDbType.VarChar, candidate.Login);

                command.CommandText = "INSERT INTO Candidates (firstname, lastname, middlename, location, locationX, locationY, expected_salary, id_act, login) OUTPUT inserted.id VALUES (@firstname, @lastname, @middlename, @location, @locationX, @locationY, @expected_salary, @id_act, @login)";
                int newID = (int)command.ExecuteScalar();
                foreach (var skill in candidate.Skills)
                {
                    AddCandidateSkillRelation(newID, skill, trans);
                }
                trans.Commit();
            }
            catch (System.Exception ex)
            {
                trans.Rollback();
                return false;
            }
            return true;
        }

        public bool Add(Vacancy vacancy)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
            SqlTransaction trans = sqlConnection.BeginTransaction();
            try
            {
                var command = sqlConnection.CreateCommand();
                command.Transaction = trans;
                AddSqlParameter(command, "@id_emp", SqlDbType.Int, vacancy.Employer.Id);
                AddSqlParameter(command, "@title", SqlDbType.VarChar, vacancy.Title);
                AddSqlParameter(command, "@id_act", SqlDbType.Int, vacancy.Activity.Id);
                AddSqlParameter(command, "@creation_date", SqlDbType.DateTime, vacancy.CreationDate);
                AddSqlParameter(command, "@location", SqlDbType.VarChar, vacancy.Location);
                AddSqlParameter(command, "@locationX", SqlDbType.Int, vacancy.Location.Length % 5);
                AddSqlParameter(command, "@locationY", SqlDbType.Int, vacancy.Location[0] % 5);
                AddSqlParameter(command, "@salary", SqlDbType.Int, vacancy.Salary);

                command.CommandText = "INSERT INTO Vacancies (title, id_emp, id_act, creation_date, location, locationX, locationY, salary) OUTPUT inserted.id VALUES (@title, @id_emp, @id_act, @creation_date, @location, @locationX, @locationY, @salary)";
                int newID = (int)command.ExecuteScalar();
                foreach (var skillID in vacancy.SkillIdsRequired)
                {
                    AddVacancySkillRelation(newID, skillID, trans);
                }
                trans.Commit();
            }
            catch (Exception)
            {
                trans.Rollback();
                return false;
            }

            return true;
        }

        public bool Add(Bargain bargain)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();
            AddSqlParameter(command, "@id_emp", SqlDbType.Int, bargain.Employer.Id);
            AddSqlParameter(command, "@id_can", SqlDbType.Int, bargain.Candidate.Id);
            AddSqlParameter(command, "@id_vac", SqlDbType.Int, bargain.Vacancy.Id);
            AddSqlParameter(command, "@bdate", SqlDbType.DateTime, bargain.DateTime);

            command.CommandText = "INSERT INTO Bargains (id_emp, id_vac, id_can, bdate) VALUES (@id_emp, @id_vac, @id_can, @bdate)";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Add(Skill skill)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();
            AddSqlParameter(command, "@title", SqlDbType.VarChar, skill.Title);
            AddSqlParameter(command, "@descr", SqlDbType.VarChar, skill.Description);

            command.CommandText = "INSERT INTO Skills (title, descr) VALUES (@title, @descr)";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Add(Activity activity)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();
            AddSqlParameter(command, "@title", SqlDbType.VarChar, activity.Title);
            AddSqlParameter(command, "@descr", SqlDbType.VarChar, activity.Description);

            command.CommandText = "INSERT INTO Activities (title, descr) VALUES (@title, @descr)";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }
        #endregion

        #region Remove

        public bool Remove(Candidate candidate)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, candidate.Id);

            command.CommandText = "DELETE FROM Candidates WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Remove(Vacancy vacancy)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, vacancy.Id);

            command.CommandText = "DELETE FROM Vacancies WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Remove(Employer employer)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, employer.Id);

            command.CommandText = "DELETE FROM Employers WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Remove(Bargain bargain)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, bargain.Id);

            command.CommandText = "DELETE FROM Bargains WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Remove(Skill skill)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, skill.Id);

            command.CommandText = "DELETE FROM Skills WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool Remove(Activity activity)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, activity.Id);

            command.CommandText = "DELETE FROM Activities WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool RemoveEmployerByName(string employerName)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, employerName);

            command.CommandText = "DELETE FROM Employers WHERE name = @name";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;

        }
        public bool RemoveVacancyByID(uint id)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }

            var command = sqlConnection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);

            command.CommandText = "DELETE FROM Vacancies WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        #endregion

        #region Check
        public bool CandidateExists(string login)
        {
            Candidate candidate = CandidateByLogin(login);
            return candidate.Id != 0;
        }

        public bool EmployerExists(string login)
        {
            Employer employer = EmployerByLogin(login);
            return employer.Id != 0;
        }
        #endregion

        private static void AddSqlParameter(SqlCommand command, String name, SqlDbType type, object value)
        {
            var parameter = new SqlParameter
            {
                ParameterName = name,
                SqlDbType = type,
                Value = value
            };

            command.Parameters.Add(parameter);
        }

    }

}
